﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-04-26
 功能说明：小品类价格带
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace PlanAPI
{
    [Description("小品类与价格带")]
    public class PLBasModelPirceHandler : BaseHandler
    {
        
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 获取价格带列表 GetPricePeriodList

        [MustLogin]
        [Description("获取价格带列表")]
        [ReturnType(typeof(PaperData<PLBasModelPirceModel.PricePeriod.ListModel>))]
        public ApiResult GetPricePeriodList()
        {
            try
            {
                var sqlStr = $"select * from ngoods.ngoods_PL_Bas_PricePeriod where CompanyID = '{UserInfo.UserCompanyId}'";
                var cmd = Helper.GetSqlStringCommand(sqlStr);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasModelPirceModel.PricePeriod.ListModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        #endregion

        #region 插入价格带数据 InsertPricePeriod

        [MustLogin]
        [Description("插入价格带数据")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult InsertPricePeriod(
            [ParamDescription("价格带")] string PriceName = "",
            [ParamDescription("开始价格")] decimal PriceBegin = 0,
            [ParamDescription("结束价格")] decimal PriceEnd = 0,
            [ParamDescription("价格带数值")] decimal PriceValue = 0)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(PriceName))
            {
                return Error($"新增失败，价格带名称不能为空");
            }

            if (PriceBegin < 0)
            {
                return Error($"新增失败，开始价格不能为空");
            }

            if (PriceEnd < 0)
            {
                return Error($"新增失败，结束价格不能为空");
            }

            if (PriceValue < 0)
            {
                return Error($"新增失败，价格带数值不能为空");
            }

            #endregion

            #region 检测该价格带是否已存在

            var sql = $"select 1 from ngoods.ngoods_PL_Bas_PricePeriod " +
                      $"where CompanyID = '{UserInfo.UserExtrSource}' and PriceName = '{PriceName}' limit 1 ";
            var o = Helper.ExecuteScalar(new VerticaCommand(sql));

            if (o != null)
            {
                return Error($"新增失败，该价格带已存在");
            }

            #endregion

            #region 插入新的价格带数据

            sql = $@"insert into 
ngoods.ngoods_PL_Bas_PricePeriod(ExtrSource,CompanyID, PriceName, PriceBegin, PriceEnd, CheckState, MofifyDTM, AllowUsed, PriceValue) 
values('{UserInfo.UserExtrSource}','{UserInfo.UserCompanyId}', '{PriceName}', {PriceBegin}, {PriceEnd}, 1, '{DateTime.Now}', 1, {PriceValue})
";
            VerticaCommand command = new VerticaCommand(sql);
            int n = Helper.ExecuteNonQuery(command);

            #endregion

            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }

        #endregion

        #region 更新价格带数据 UpdatePricePeriod 
 
        [MustLogin]
        [Description("更新价格带")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdatePricePeriod(
            [ParamDescription("价格带")] string PriceName = "",
            [ParamDescription("开始价格")] decimal PriceBegin = 0,
            [ParamDescription("结束价格")] decimal PriceEnd = 0,
            [ParamDescription("价格带数值")] decimal PriceValue = 0,
            [ParamDescription("价格带")] string originalPriceName = "")
        {
            #region 前端参数验证
            if (string.IsNullOrWhiteSpace(originalPriceName))
            {
                return Error($"更新失败，参数解析失败，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(PriceName))
            {
                return Error($"更新失败，价格带名称不能为空");
            }

            if (PriceBegin < 0)
            {
                return Error($"更新失败，开始价格不能为空");
            }

            if (PriceEnd < 0)
            {
                return Error($"更新失败，结束价格不能为空");
            }

            if (PriceValue < 0)
            {
                return Error($"更新失败，价格带数值不能为空");
            }

            #endregion

            #region 修改价格带

            string sql = $@"update ngoods.ngoods_PL_Bas_PricePeriod 
set
PriceBegin={PriceBegin}, 
PriceEnd={PriceEnd}, 
CheckState=1, 
MofifyDTM='{DateTime.Now}', 
AllowUsed=1, 
PriceValue={PriceValue}
where CompanyID = '{UserInfo.UserCompanyId}' and  PriceName='{originalPriceName}'
";
            int n = Helper.ExecuteNonQuery(new VerticaCommand(sql));

            #endregion

            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }

        #endregion

        #region 根据价格带获取小品类数据列表 GetModelPirceList

        [MustLogin]
        [Description("获取小品类与价格带列表")]
        [ReturnType(typeof(PaperData<PLBasModelPirceModel.ModelPirce.ListModel>))]
        public ApiResult GetModelPirceList(
            [ParamDescription("价格带")] string PriceName = "",
            //[ParamDescription("类型")] string KindID = "",
            [ParamDescription("品类")] string ModelID = "",
            [ParamDescription("小品类")] string ItemID = "")
        {
            if (string.IsNullOrWhiteSpace(PriceName))
            {
                return Success(new List<string>());
            }

            string whereStr = " ";

            //加入前台传来的参数
            //whereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and mp.KindID = '{KindID}'";
            whereStr += string.IsNullOrWhiteSpace(ModelID) ? "" : $" and mat.ModelID = '{ModelID}'";
            whereStr += string.IsNullOrWhiteSpace(ItemID) ? "" : $" and item.ItemID = '{ItemID}'";

            try
            {


                var sql = $@"select 
case when item.ItemID in (select ItemID from ngoods.ngoods_PL_Bas_ModelPirce where CompanyID = '{UserInfo.UserCompanyId}' and  PriceName='{PriceName}') then true
else false end as Selected ,
mp.PriceName,pp.PriceValue ,mat.ModelID,mat.ModelName,item.ItemID,item.ItemName 
from nods.nods_erp_SD_Mat_Model mat
inner join nods.nods_erp_SD_Mat_Item item on mat.ExtrSource = item.ExtrSource and mat.ModelCode=left(item.ItemCode,1)
left join ngoods.ngoods_PL_Bas_ModelPirce mp on item.ExtrSource = mp.CompanyID and item.ItemID = mp.ItemID and PriceName = '{PriceName}'
left join ngoods.ngoods_PL_Bas_PricePeriod pp on mp.CompanyID = pp.CompanyID and mp.PriceName = pp.PriceName
where mat.ExtrSource = '{UserInfo.UserExtrSource}' and mat.ModelID in (select ConID from ngoods.ngoods_PL_PlanMatConSet where CompanyID = '{UserInfo.UserCompanyId}' and  MatConFlag=4)
{whereStr}
order by mat.ModelID,item.ItemID
";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasModelPirceModel.ModelPirce.ListModel>.ConvertToModel(dt);
                foreach (var model in list)
                {
                    model.CompanyID = UserInfo.UserCompanyId;//默认KM
                    model.KindID = "000001";//默认服装
                    model.PriceName = PriceName;
                }
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        #endregion

        #region 设置小品类与价格带数据 SetModelPirceList

        [MustLogin]
        [Description("保存小品类与价格带")]
        [JsonType]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult SetPirceList(PLBasModelPirceModel.ModelPirce.UpdateModelList list)
        {
            if (list?.List == null)
            {
                return Error("接收参数错误，请刷新重试");
            }

            var item = list.List[0];

            try
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();

                var deleteSql = $@"delete from ngoods.ngoods_PL_Bas_ModelPirce where CompanyID = '{UserInfo.UserCompanyId}' and PriceName = {item.PriceName}";
                cmdList.Add(new CommandInfo(deleteSql, null));

                foreach (var model in list.List)
                {
                    var sql = $@"
insert into ngoods.ngoods_PL_Bas_ModelPirce(ExtrSource,CompanyID,KindID,ModelID,ItemID,PriceName,CheckState,MofifyDTM,AllowUsed) 
values ('{UserInfo.UserExtrSource}','{model.CompanyID}','{model.KindID}','{model.ModelID}','{model.ItemID}',{model.PriceName},1,'{DateTime.Now}',1)
";
                    cmdList.Add(new CommandInfo(sql, null));
                }

                int n = Helper.ExecuteSqlTran(cmdList);
                if (n<=0)
                {
                    return Error("保存失败，请重试");
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        #endregion

    }

}
